package auxiliary;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;

public class Database {

	ArrayList<ArrayList<String>> listOfFuncionarios;
	ArrayList<ArrayList<String>> listOfProductos;
	ArrayList<ArrayList<String>> listOfMaquinarias;
	ArrayList<ArrayList<String>> listOfMaquinariaValues;
	ArrayList<ArrayList<String>> listOfMensuales;
	Connection conn = null;

	public Database() {
		
		try {
			prepareConnection();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	public void prepareConnection() throws Exception {
		// try {
		String userName = "root";
		String password = "root";
		String url = "jdbc:mysql://127.0.0.1/gerco";
		Class.forName("com.mysql.jdbc.Driver").newInstance();
		conn = DriverManager.getConnection(url, userName, password);
		// //System.out.println ("Database connection established");
		// }
		// catch(Exception e) {
		// throws new Exception
		// System.err.println ("Cannot connect to database server");
		// }
	}

	public void closeConnection() {
		if (conn != null) {
			try {
				conn.close();
				// //System.out.println ("Database connection terminated");
			} catch (Exception e) { /* ignore close errors */
			}
		}
	}

	// // FUNCIONARIO METHODS

	public synchronized void insertFuncionario(
			ArrayList<String> funcionarioData, ArrayList<String> maquinariaData) {

		try {

			//prepareConnection();

			Statement s = conn.createStatement();
			s
					.executeUpdate("INSERT INTO funcionario (codigo, nombre, apellido, cargo, dispensacion, produccion, llenado,"
							+ "loteado,etiqueteado,bandas,empacado,limpieza" /*
																			 * +
																			 * "disponibilidad, tiempo_muerto"
																			 */
							+ ") VALUES ("
							+ funcionarioData.get(0)
							+ ",'"
							+ funcionarioData.get(1)
							+ "','"
							+ funcionarioData.get(2)
							+ "','"
							+ funcionarioData.get(3)
							+ "',"
							+ funcionarioData.get(4)
							+ ","
							+ funcionarioData.get(5)
							+ ","
							+ funcionarioData.get(6)
							+ ","
							+ funcionarioData.get(7)
							+ ","
							+ funcionarioData.get(8)
							+ ","
							+ funcionarioData.get(9)
							+ ","
							+ funcionarioData.get(10)
							+ ","
							+ funcionarioData.get(11) +
							/*
							 * + "'," + funcionarioData.get(4) + "," +
							 * funcionarioData.get(5)
							 */")");

			s
					.executeQuery("SELECT id AS LastID FROM funcionario WHERE id = @@Identity");

			ResultSet rs = s.getResultSet();
			int id = 0;
			while (rs.next()) {

				id = rs.getInt("LastID");

			}

			for (int i = 0; i < maquinariaData.size(); i++) {
				s
						.executeUpdate("INSERT INTO capacidad_de_operar (maquina_id,funcionario_id) "
								+ "VALUES ("
								+ maquinariaData.get(i)
								+ ","
								+ id
								+ ")");
			}
			s.close();

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			////closeConnection();
		}
	}

	public synchronized void deleteFuncionario(String[] values) {

		try {

			//prepareConnection();

			// listOfFuncionarios = new ArrayList<ArrayList<String>>();

			Statement s = conn.createStatement();
			s.executeUpdate("DELETE FROM funcionario WHERE id ='" + values[0]
					+ "'");
			s.close();

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}
	}

	public synchronized void modifyFuncionario(String[] values,
			String[] maquinariaValues, boolean[] maqModifications) {
		

			//prepareConnection();
		Statement s = null;
		try {
			s = conn.createStatement();
		
		
		try {
			
			
			String statement = "UPDATE funcionario SET codigo=" + values[1]
					+ ", nombre='" + values[2] + "', apellido='" + values[3]
					+ "', cargo='" + values[4] + "', dispensacion = "
					+ values[5] + ", produccion = " + values[6]
					+ ", llenado = " + values[7] + ",loteado = " + values[8]
					+ ", etiqueteado = " + values[9] + ",bandas = "
					+ values[10] + ",empacado = " + values[11] + ",limpieza = "
					+ values[12] + " WHERE id=" + values[0];
			////System.out.println(statement);
			s.executeUpdate(statement);
		
		} catch(com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException e) {
			//System.err.println("hola mijo");
			//e.printStackTrace();
		}
		catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}

			//System.out.println("size of maqmod" + maqModifications.length);
			//System.out.println("size of values" + maquinariaValues.length);
			for (int i = 0; i < maqModifications.length; i++) {
				if (maqModifications[i] == false) {
					try {
						String statement = "DELETE FROM capacidad_de_operar WHERE maquina_id ="
								+ maquinariaValues[i]
								+ " AND funcionario_id ="
								+ values[0];
						//System.out.println("falsequery=" + statement);
						s.executeUpdate(statement);
					} catch(com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException e) {
						//System.err.println("hola mijo");
						//e.printStackTrace();
					}
					catch (Exception e) {
						System.err.println("Cannot connect to database server");
						e.printStackTrace();
					} finally {
						//closeConnection();
					}
				}
				else {
					try {
						String statement = "INSERT INTO capacidad_de_operar (maquina_id,funcionario_id) "
							+ "VALUES ("
							+ maquinariaValues[i]
							+ ","
							+ values[0]
							+ ")";
						//System.out.println("truequery=" + statement);
						s.executeUpdate(statement);
					} catch(com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException e) {
						//System.err.println("hola mijo");
						//e.printStackTrace();
					}
					catch (Exception e) {
						System.err.println("Cannot connect to database server");
						e.printStackTrace();
					} finally {
						//closeConnection();
					}
				}
				//System.out.println("i=" + i);
			}
			
			s.close();
			}
			catch(com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException e) {
				//System.err.println("hola mijo");
				//e.printStackTrace();
			}
			catch (Exception e) {
				System.err.println("Cannot connect to database server");
				e.printStackTrace();
			} finally {
				//closeConnection();
			}
			// //System.out.println(statement);
			
			// //System.out.println("updateattempted id=" + values[0]);

		
	}

	public synchronized ArrayList<ArrayList<String>> retrieveFuncionarios() {

		try {

			//prepareConnection();

			listOfFuncionarios = new ArrayList<ArrayList<String>>();
			listOfMaquinariaValues = new ArrayList<ArrayList<String>>();

			Statement s = conn.createStatement();
			s
					.executeQuery("SELECT id, codigo, nombre, apellido, cargo, dispensacion, produccion, llenado,"
							+ "loteado,etiqueteado,bandas,empacado,limpieza FROM funcionario");
			ResultSet rs = s.getResultSet();

			while (rs.next()) {
				ArrayList<String> singleList = new ArrayList<String>();
				ArrayList<String> valueList = new ArrayList<String>();

				int id = rs.getInt("id");

				singleList.add(Integer.toString(id));
				singleList.add(Integer.toString(rs.getInt("codigo")));
				singleList.add(rs.getString("nombre"));
				singleList.add(rs.getString("apellido"));
				singleList.add(rs.getString("cargo"));
				singleList.add(rs.getInt("dispensacion") == 1 ? "Si" : "No");
				singleList.add(rs.getInt("produccion") == 1 ? "Si" : "No");
				singleList.add(rs.getInt("llenado") == 1 ? "Si" : "No");
				singleList.add(rs.getInt("loteado") == 1 ? "Si" : "No");
				singleList.add(rs.getInt("etiqueteado") == 1 ? "Si" : "No");
				singleList.add(rs.getInt("bandas") == 1 ? "Si" : "No");
				singleList.add(rs.getInt("empacado") == 1 ? "Si" : "No");
				singleList.add(rs.getInt("limpieza") == 1 ? "Si" : "No");

				Statement s2 = conn.createStatement();
				s2
						.executeQuery("SELECT DISTINCT modelo, maquina_id FROM capacidad_de_operar co, "
								+ "maquina mq WHERE co.maquina_id=mq.id AND co.funcionario_id = "
								+ id);

				ResultSet secondset = s2.getResultSet();

				String maquinas = "";
				while (secondset.next()) {
					maquinas += ",";
					maquinas += secondset.getString("modelo");
					// valueList
					// .add(secondset.getString("tiempo_unidades"));
				}

				maquinas = maquinas.replaceFirst(",", "");

				singleList.add(maquinas);

				// listOfMaquinariaValues.add(valueList);

				// QUERY de maquinarias seleccionadas para loteado

				valueList.clear();

				listOfFuncionarios.add(singleList);

			}
			rs.close();
			s.close();

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}

		return listOfFuncionarios;
	}
	
	public synchronized ArrayList<String> retrieveFuncionarioIds() {

		ArrayList<String> listOfIds = new ArrayList<String>();

		try {

			//prepareConnection();

			Statement s = conn.createStatement();
			String query = "SELECT id FROM funcionario";
			
			s.executeQuery(query);

			ResultSet rs = s.getResultSet();

			while (rs.next()) {

				int id = rs.getInt("id");

				listOfIds.add(String.valueOf(id));

			}
			rs.close();
			s.close();

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}

		return listOfIds;
	}

	public synchronized HashMap<String, ArrayList<String>> retrieveMapFuncionarios() {

		HashMap<String, ArrayList<String>> list = new HashMap<String, ArrayList<String>>();

		try {

			//prepareConnection();

			listOfFuncionarios = new ArrayList<ArrayList<String>>();
			listOfMaquinariaValues = new ArrayList<ArrayList<String>>();

			Statement s = conn.createStatement();
			s
					.executeQuery("SELECT id, codigo, nombre, apellido, cargo, dispensacion, produccion, llenado,"
							+ "loteado,etiqueteado,bandas,empacado,limpieza FROM funcionario");
			ResultSet rs = s.getResultSet();

			while (rs.next()) {
				ArrayList<String> singleList = new ArrayList<String>();
				ArrayList<String> valueList = new ArrayList<String>();

				int id = rs.getInt("id");

				singleList.add(Integer.toString(id));
				singleList.add(Integer.toString(rs.getInt("codigo")));
				singleList.add(rs.getString("nombre"));
				singleList.add(rs.getString("apellido"));
				singleList.add(rs.getString("cargo"));
				singleList.add(String.valueOf(rs.getInt("dispensacion")));
				singleList.add(String.valueOf(rs.getInt("produccion")));
				singleList.add(String.valueOf(rs.getInt("llenado")));
				singleList.add(String.valueOf(rs.getInt("loteado")));
				singleList.add(String.valueOf(rs.getInt("etiqueteado")));
				singleList.add(String.valueOf(rs.getInt("bandas")));
				singleList.add(String.valueOf(rs.getInt("empacado")));
				singleList.add(String.valueOf(rs.getInt("limpieza")));

				list.put(String.valueOf(id), singleList);

			}
			rs.close();
			s.close();

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}

		return list;
	}

	public synchronized ArrayList<String> retrieveFuncionarioCapacidadOperar(
			int maquinaid) {

		ArrayList<String> listOfIds = new ArrayList<String>();

		try {

			//prepareConnection();

			Statement s = conn.createStatement();
			String query = "SELECT funcionario_id FROM capacidad_de_operar WHERE maquina_id = "
					+ maquinaid;

			// //System.out.println(query);
			s.executeQuery(query);

			ResultSet rs = s.getResultSet();

			while (rs.next()) {

				int id = rs.getInt("funcionario_id");

				listOfIds.add(String.valueOf(id));

			}
			rs.close();
			s.close();

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}

		return listOfIds;
	}
	
	public synchronized ArrayList<String> retrieveFuncionarioCapacidadEtiquetar() {

		ArrayList<String> listOfIds = new ArrayList<String>();

		try {

			//prepareConnection();

			Statement s = conn.createStatement();
			String query = "SELECT id FROM funcionario WHERE etiqueteado = 1";
			
			s.executeQuery(query);

			ResultSet rs = s.getResultSet();

			while (rs.next()) {

				int id = rs.getInt("id");

				listOfIds.add(String.valueOf(id));

			}
			rs.close();
			s.close();

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}

		return listOfIds;
	}
	
	public synchronized ArrayList<String> retrieveFuncionarioCapacidadBandas() {

		ArrayList<String> listOfIds = new ArrayList<String>();

		try {

			//prepareConnection();

			Statement s = conn.createStatement();
			String query = "SELECT id FROM funcionario WHERE bandas = 1";
			
			s.executeQuery(query);

			ResultSet rs = s.getResultSet();

			while (rs.next()) {

				int id = rs.getInt("id");

				listOfIds.add(String.valueOf(id));

			}
			rs.close();
			s.close();

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}

		return listOfIds;
	}

	// MAQUINA METHODS

	public synchronized void insertMaquinaria(ArrayList<String> maquinariaData) {

		try {

			//prepareConnection();

			Statement s = conn.createStatement();
			// //System.out.println("maquinariadata" + maquinariaData.get(0) + ":"
			// + maquinariaData.get(1));
			s
					.executeUpdate("INSERT INTO maquina (modelo, produccion, llenado, loteado, capacidad, numero_funcionarios) VALUES ('"
							+ maquinariaData.get(0)
							+ "',"
							+ maquinariaData.get(1)
							+ ","
							+ maquinariaData.get(2)
							+ ","
							+ maquinariaData.get(3)
							+ ","
							+ maquinariaData.get(4)
							+ ","
							+ maquinariaData.get(5) + ")");

			// ResultSet rs = s.getResultSet ();
			s.close();

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}
	}

	public synchronized boolean deleteMaquinaria(String[] values) {

		try {

			//prepareConnection();

			// listOfFuncionarios = new ArrayList<ArrayList<String>>();

			Statement s = conn.createStatement();
			s
					.executeUpdate("DELETE FROM maquina WHERE id ='"
							+ values[0] + "'");
			s.close();

		} catch (Exception e) {
			// System.err.println("Delete maquinaria error");
			// e.printStackTrace();
			return false;
		} finally {
			//closeConnection();
		}
		return true;
	}

	public synchronized void modifyMaquinaria(String[] values) {
		try {

			//prepareConnection();

			Statement s = conn.createStatement();
			String statement = "UPDATE maquina SET modelo='" + values[1]
					+ "', produccion=" + values[2] + ",llenado=" + values[3]
					+ ", loteado=" + values[4] + ", capacidad =" + values[5]
					+ ", numero_funcionarios=" + values[6] + " WHERE id="
					+ values[0];
			//System.out.println(statement);
			s.executeUpdate(statement);
			// //System.out.println(statement);
			s.close();
			// //System.out.println("updateattempted id=" + values[0]);

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}
	}

	public synchronized ArrayList<ArrayList<String>> retrieveMaquinaria() {
		try {

			//prepareConnection();

			listOfMaquinarias = new ArrayList<ArrayList<String>>();

			Statement s = conn.createStatement();
			s
					.executeQuery("SELECT id, modelo, capacidad, produccion, llenado, loteado, numero_funcionarios FROM maquina");
			ResultSet rs = s.getResultSet();

			while (rs.next()) {
				ArrayList<String> singleList = new ArrayList<String>();

				int id = rs.getInt("id");
				singleList.add(Integer.toString(id));
				singleList.add(rs.getString("modelo"));
				singleList.add(rs.getInt("produccion") == 1 ? "Si" : "No");
				singleList.add(rs.getInt("llenado") == 1 ? "Si" : "No");
				singleList.add(rs.getInt("loteado") == 1 ? "Si" : "No");
				singleList.add(Integer.toString(rs.getInt("capacidad")));
				singleList.add(Integer.toString(rs
						.getInt("numero_funcionarios")));

				Statement s2 = conn.createStatement();
				s2
						.executeQuery("SELECT DISTINCT fun.nombre AS nombre, fun.apellido "
								+ "AS apellido FROM capacidad_de_operar co, "
								+ "maquina mq, funcionario fun WHERE co.funcionario_id=fun.id "
								+ "AND co.maquina_id = mq.id AND mq.id =" + id);

				ResultSet secondset = s2.getResultSet();

				String funcionarios = "";
				while (secondset.next()) {
					funcionarios += ",";
					funcionarios += " " + secondset.getString("nombre") + " "
							+ secondset.getString("apellido");
				}

				funcionarios = funcionarios.replaceFirst(",", "");

				singleList.add(funcionarios);
				
				//System.out.println(singleList);

				listOfMaquinarias.add(singleList);

			}
			rs.close();
			s.close();

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}

		return listOfMaquinarias;
	}

	public synchronized ArrayList<ArrayList<String>> retrieveMaquinaria(int type) {

		try {

			//prepareConnection();

			listOfMaquinarias = new ArrayList<ArrayList<String>>();

			Statement s = conn.createStatement();
			String clause;

			if (type == 0) {
				clause = "produccion = 1";
			} else if (type == 1) {
				clause = "llenado = 1";
			} else if (type == 2) {
				clause = "loteado = 1";
			} else
				clause = "produccion = 1 OR produccion = 0";

			s
					.executeQuery("SELECT id, modelo, capacidad, produccion, llenado, loteado FROM maquina WHERE "
							+ clause + " ORDER BY capacidad DESC");
			ResultSet rs = s.getResultSet();

			while (rs.next()) {
				ArrayList<String> singleList = new ArrayList<String>();

				int id = rs.getInt("id");
				singleList.add(Integer.toString(id));
				singleList.add(rs.getString("modelo"));
				singleList.add(rs.getInt("produccion") == 1 ? "Si" : "No");
				singleList.add(rs.getInt("llenado") == 1 ? "Si" : "No");
				singleList.add(rs.getInt("loteado") == 1 ? "Si" : "No");
				singleList.add(Integer.toString(rs.getInt("capacidad")));

				Statement s2 = conn.createStatement();
				s2
						.executeQuery("SELECT DISTINCT fun.nombre AS nombre, fun.apellido "
								+ "AS apellido FROM capacidad_de_operar co, "
								+ "maquina mq, funcionario fun WHERE co.funcionario_id=fun.id "
								+ "AND co.maquina_id = mq.id AND mq.id =" + id);

				ResultSet secondset = s2.getResultSet();

				String funcionarios = "";
				while (secondset.next()) {
					funcionarios += ",";
					funcionarios += " " + secondset.getString("nombre") + " "
							+ secondset.getString("apellido");
				}

				funcionarios = funcionarios.replaceFirst(",", "");

				singleList.add(funcionarios);

				listOfMaquinarias.add(singleList);

			}
			rs.close();
			s.close();

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}

		return listOfMaquinarias;
	}

	public synchronized HashMap<String, ArrayList<String>> retrieveMapMaquinaria(
			int type) {

		HashMap<String, ArrayList<String>> list = new HashMap<String, ArrayList<String>>();

		try {

			//prepareConnection();

			listOfMaquinarias = new ArrayList<ArrayList<String>>();

			Statement s = conn.createStatement();
			String clause;

			if (type == 0) {
				clause = "produccion = 1";
			} else if (type == 1) {
				clause = "llenado = 1";
			} else if (type == 2) {
				clause = "loteado = 1";
			} else
				clause = "produccion = 1 OR produccion = 0";

			s
					.executeQuery("SELECT id, modelo, capacidad, produccion, llenado, loteado FROM maquina WHERE "
							+ clause + " ORDER BY capacidad");
			ResultSet rs = s.getResultSet();

			while (rs.next()) {
				ArrayList<String> singleList = new ArrayList<String>();

				int id = rs.getInt("id");
				singleList.add(Integer.toString(id));
				singleList.add(rs.getString("modelo"));
				singleList.add(rs.getInt("produccion") == 1 ? "Si" : "No");
				singleList.add(rs.getInt("llenado") == 1 ? "Si" : "No");
				singleList.add(rs.getInt("loteado") == 1 ? "Si" : "No");
				singleList.add(Integer.toString(rs.getInt("capacidad")));

				Statement s2 = conn.createStatement();
				s2
						.executeQuery("SELECT DISTINCT fun.nombre AS nombre, fun.apellido "
								+ "AS apellido FROM capacidad_de_operar co, "
								+ "maquina mq, funcionario fun WHERE co.funcionario_id=fun.id "
								+ "AND co.maquina_id = mq.id AND mq.id =" + id);

				ResultSet secondset = s2.getResultSet();

				String funcionarios = "";
				while (secondset.next()) {
					funcionarios += ",";
					funcionarios += " " + secondset.getString("nombre") + " "
							+ secondset.getString("apellido");
				}

				funcionarios = funcionarios.replaceFirst(",", "");

				singleList.add(funcionarios);

				list.put(String.valueOf(id), singleList);

			}
			rs.close();
			s.close();

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}

		return list;
	}

	public synchronized ArrayList<String> retrieveMaquinariaByFase(
			int productid, int fase) {

		ArrayList<String> listOfIds = new ArrayList<String>();

		try {

			//prepareConnection();

			Statement s = conn.createStatement();
			String query = "SELECT cp.maquina_id FROM capacidad_de_producir cp, maquina m WHERE m.id = cp.maquina_id AND "
					+ "cp.tipo_producto_id = "
					+ productid
					+ " AND cp.fase = "
					+ fase + " ORDER BY m.capacidad DESC";
			// //System.out.println(query);
			s.executeQuery(query);

			ResultSet rs = s.getResultSet();

			while (rs.next()) {

				int id = rs.getInt("maquina_id");

				listOfIds.add(String.valueOf(id));

			}
			rs.close();
			s.close();

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}

		return listOfIds;
	}
	
	public synchronized ArrayList<String> retrieveMaquinariaByFase(
			int productid, int fase,int litros) {

		ArrayList<String> listOfIds = new ArrayList<String>();

		try {

			//prepareConnection();

			Statement s = conn.createStatement();
			String query = "SELECT cp.maquina_id FROM capacidad_de_producir cp, maquina m WHERE m.id = cp.maquina_id AND "
					+ "cp.tipo_producto_id = "
					+ productid
					+ " AND cp.fase = "
					+ fase + " AND m.capacidad >= " + litros + " ORDER BY m.capacidad DESC";
			//System.out.println(query);
			s.executeQuery(query);

			ResultSet rs = s.getResultSet();

			while (rs.next()) {

				int id = rs.getInt("maquina_id");

				listOfIds.add(String.valueOf(id));

			}
			rs.close();
			s.close();

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}

		return listOfIds;
	}

	public synchronized int retrieveMaquinariaNumFuncionarios(int maquinariaid) {

		int numero = 0;

		try {

			//prepareConnection();

			Statement s = conn.createStatement();
			String query = "SELECT numero_funcionarios FROM maquina WHERE id="
					+ maquinariaid;

			////System.out.println(query);
			s.executeQuery(query);

			ResultSet rs = s.getResultSet();

			while (rs.next()) {

				numero = rs.getInt("numero_funcionarios");

			}
			rs.close();
			s.close();

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}

		return numero;
	}

	public synchronized void insertProducto(ArrayList<String> productoData,
			ArrayList<String> fasesData,
			ArrayList<String> maquinariaProduccionData,
			ArrayList<String> maquinariaLlenadoData,
			ArrayList<String> maquinariaLoteadoData) {

		try {

			//prepareConnection();

			Statement s = conn.createStatement();

			// insertar en la tabla tipo_producto informacion del producto
			// creado
			String unidadesetiquetadas ="0";
			String unidadesbandas ="0";
			if(productoData.get(4).length()!=0) {
				unidadesetiquetadas = productoData.get(4);
			}
			if(productoData.get(5).length()!=0) {
				unidadesbandas = productoData.get(5);
			}

			String query = "INSERT INTO tipo_producto (nombre, unidad, tiempo_dispensacion_lote, tiempo_produccion_lote "
				+ ",unidades_etiquetadas_hora, unidades_bandas_hora, unidades_empacadas_hora, loteado, etiquetado, bandas) "
				+ "VALUES ('"
				+ productoData.get(0)
				+ "',"
				+ productoData.get(1)
				+ ","
				+ productoData.get(2)
				+ ","
				+ productoData.get(3)
				+ ","
				+ unidadesetiquetadas
				+ ","
				+ unidadesbandas
				+ "," 
				+ productoData.get(6) 
				+ "," 
				+ fasesData.get(2) 
				+ "," 
				+ fasesData.get(0) 
				+ "," 
				+ fasesData.get(1) 
				+ ")";
			
				//System.out.println(query);
			s
					.executeUpdate(query);

			s
					.executeQuery("SELECT id AS LastID FROM tipo_producto WHERE id = @@Identity");

			ResultSet rs = s.getResultSet();
			int id = 0;
			while (rs.next()) {

				id = rs.getInt("LastID");

			}

			for (int i = 0; i < maquinariaProduccionData.size(); i++) {
				s
						.executeUpdate("INSERT INTO capacidad_de_producir (maquina_id,tipo_producto_id,fase,"
								+ "tiempo_unidades) "
								+ "VALUES ("
								+ maquinariaProduccionData.get(i)
								+ ","
								+ id
								+ ",3,1)");
			}

			for (int i = 0; i < maquinariaLlenadoData.size(); i++) {
				s
						.executeUpdate("INSERT INTO capacidad_de_producir (maquina_id,tipo_producto_id,fase,"
								+ "tiempo_unidades) "
								+ "VALUES ("
								+ maquinariaLlenadoData.get(i)
								+ ","
								+ id
								+ ",4,1)");
			}

			for (int i = 0; i < maquinariaLoteadoData.size(); i++) {
				s
						.executeUpdate("INSERT INTO capacidad_de_producir (maquina_id,tipo_producto_id,fase,"
								+ "tiempo_unidades) "
								+ "VALUES ("
								+ maquinariaLoteadoData.get(i)
								+ ","
								+ id
								+ ",5,1)");
			}
			s.close();

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}
	}

	public synchronized boolean deleteProducto(String[] values) {

		try {

			//prepareConnection();

			// listOfFuncionarios = new ArrayList<ArrayList<String>>();

			Statement s = conn.createStatement();
			s.executeUpdate("DELETE FROM tipo_producto WHERE id ='" + values[0]
					+ "'");
			s.close();

		} catch (Exception e) {
			// System.err.println("Error on delete");
			// e.printStackTrace();
			return false;
		} finally {
			//closeConnection();
		}
		return true;
	}

	public synchronized void modifyProducto(String[] values, 
			ArrayList<String> fasesData,
			String[] fases,
			String[] maquinariaValues, String[] maqValues,
			boolean[] maqModifications) {
		try {

			//prepareConnection();

			/*
			 * //System.out.println("values=" + Arrays.toString(values));
			 * //System.out.println("fases=" + Arrays.toString(fases));
			 * //System.out.println("maqValues=" + Arrays.toString(maqValues));
			 * //System.out.println("maquinariaValues=" +
			 * Arrays.toString(maquinariaValues));
			 */

			Statement s = conn.createStatement();
			String statement = "UPDATE tipo_producto SET nombre='" + values[1]
					+ "', unidad='" + values[2]
					+ "', tiempo_dispensacion_lote=" + values[3]
					+ ", tiempo_produccion_lote=" + values[4]
					+ ", unidades_etiquetadas_hora=" + values[5]
					+ ", unidades_bandas_hora=" + values[6]
					+ ", unidades_empacadas_hora=" + values[7] 
					+ ", loteado=" + fasesData.get(0) 
					+ ", etiquetado=" + fasesData.get(1) 
					+ ", bandas=" + fasesData.get(2) 
					+ " WHERE id="
					+ values[0];
			s.executeUpdate(statement);
			for (int i = 0; i < maqValues.length; i++) {
				statement = "UPDATE capacidad_de_producir SET tiempo_unidades="
						+ maqValues[i] + " WHERE maquina_id="
						+ maquinariaValues[i] + " AND tipo_producto_id ="
						+ values[0] + " AND fase =" + fases[i];
				s.executeUpdate(statement);
			}
			for (int i = 0; i < maqModifications.length; i++) {
				if (maqModifications[i] == false) {
					statement = "DELETE FROM capacidad_de_producir WHERE maquina_id ="
							+ maquinariaValues[i]
							+ " AND tipo_producto_id ="
							+ values[0] + " AND fase =" + fases[i];
					s.executeUpdate(statement);
				}
			}

			// //System.out.println(statement);
			s.close();
			// //System.out.println("updateattempted id=" + values[0]);

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}
	}

	public synchronized ArrayList<ArrayList<String>> retrieveProductos() {

		try {

			//prepareConnection();

			listOfProductos = new ArrayList<ArrayList<String>>();
			listOfMaquinariaValues = new ArrayList<ArrayList<String>>();

			Statement s = conn.createStatement();
			s
					.executeQuery("SELECT id,nombre, unidad, tiempo_dispensacion_lote, tiempo_produccion_lote, "
							+ "unidades_etiquetadas_hora, unidades_bandas_hora, "
							+ "unidades_empacadas_hora, loteado, etiquetado, bandas FROM tipo_producto");
			ResultSet rs = s.getResultSet();

			while (rs.next()) {

				ArrayList<String> singleList = new ArrayList<String>();
				ArrayList<String> valueList = new ArrayList<String>();

				// ArrayList<String> secondList = new ArrayList<String>();

				int id = rs.getInt("id");

				singleList.add(Integer.toString(id));
				singleList.add(rs.getString("nombre"));
				//singleList.add(Integer.toString(rs.getInt("unidad")));
				singleList.add(Integer.toString(rs.getInt("unidad"))
						+ " cc");
				singleList.add(Float.toString(rs
						.getFloat("tiempo_dispensacion_lote")));
				singleList.add(Float.toString(rs
						.getFloat("tiempo_produccion_lote")));
				singleList.add(Integer.toString(rs
						.getInt("unidades_etiquetadas_hora")));
				singleList.add(Integer.toString(rs
						.getInt("unidades_bandas_hora")));
				singleList.add(Integer.toString(rs
						.getInt("unidades_empacadas_hora")));

				// QUERY de maquinarias seleccionadas para produccion

				Statement s2 = conn.createStatement();
				s2
						.executeQuery("SELECT DISTINCT modelo, tiempo_unidades, fase, maquina_id FROM capacidad_de_producir cp, "
								+ "maquina mq WHERE cp.maquina_id=mq.id AND (cp.fase = 3 OR cp.fase = 4 OR cp.fase = 5) "
								+ "AND cp.tipo_producto_id = "
								+ id
								+ " ORDER BY fase");

				ResultSet secondset = s2.getResultSet();

				String maqProduccion = "";
				String maqLlenado = "";
				String maqLoteado = "";
				while (secondset.next()) {
					if (secondset.getString("fase").matches("3")) {
						maqProduccion += ",";
						maqProduccion += secondset.getString("modelo");
						// valueList
						// .add(secondset.getString("tiempo_unidades"));
					} else if (secondset.getString("fase").matches("4")) {
						// listOfMaquinariaValues.add(valueList);
						// valueList.clear();
						maqLlenado += ",";
						maqLlenado += secondset.getString("modelo");
						// valueList
						// .add(secondset.getString("tiempo_unidades"));
					} else if (secondset.getString("fase").matches("5")) {
						// listOfMaquinariaValues.add(valueList);
						// valueList.clear();
						maqLoteado += ",";
						maqLoteado += secondset.getString("modelo");
						// valueList
						// .add(secondset.getString("tiempo_unidades"));
					}
				}

				maqProduccion = maqProduccion.replaceFirst(",", "");
				maqLlenado = maqLlenado.replaceFirst(",", "");
				maqLoteado = maqLoteado.replaceFirst(",", "");

				singleList.add(maqProduccion);
				singleList.add(maqLlenado);
				singleList.add(maqLoteado);
				
				singleList.add(Integer.toString(rs
						.getInt("loteado")));
				singleList.add(Integer.toString(rs
						.getInt("etiquetado")));
				singleList.add(Integer.toString(rs
						.getInt("bandas")));

				// listOfMaquinariaValues.add(valueList);

				// QUERY de maquinarias seleccionadas para loteado

				valueList.clear();

				listOfProductos.add(singleList);
				// //System.out.println("singlelist = " + singleList.toString());

				secondset.close();
				s2.close();
				// s3.close();
				// s4.close();
			}
			rs.close();
			s.close();

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}

		return listOfProductos;
	}

	public boolean retrieveProductoRequiereLoteado(String productoid) {
		boolean loteado = false;

		try {

			//prepareConnection();

			Statement s = conn.createStatement();
			
			s
					.executeQuery("SELECT loteado FROM tipo_producto "
							+ "WHERE id = " + productoid);
			ResultSet rs = s.getResultSet();

			while (rs.next()) {

				loteado = rs.getBoolean("loteado");

			}
			rs.close();
			s.close();

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			if(conn == null){
				//System.out.println("conn is null");
			}
			else {
				//System.out.println("conn is not null");
			}
			e.printStackTrace();
		} finally {
			//closeConnection();
		}

		return loteado;
	}
	
	public boolean retrieveProductoRequiereEtiquetado(String productoid) {
		boolean etiquetado = false;

		try {

			//prepareConnection();

			Statement s = conn.createStatement();
			s
					.executeQuery("SELECT etiquetado FROM tipo_producto "
							+ "WHERE id = " + productoid);
			ResultSet rs = s.getResultSet();

			while (rs.next()) {

				etiquetado = rs.getBoolean("etiquetado");

			}
			rs.close();
			s.close();

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}

		return etiquetado;
	}
	
	public boolean retrieveProductoRequiereBandas(String productoid) {
		boolean bandas = false;

		try {

			//prepareConnection();

			Statement s = conn.createStatement();
			s
					.executeQuery("SELECT bandas FROM tipo_producto "
							+ "WHERE id = " + productoid);
			ResultSet rs = s.getResultSet();

			while (rs.next()) {

				bandas = rs.getBoolean("bandas");

			}
			rs.close();
			s.close();

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}

		return bandas;
	}
	
	public float retrieveProductoDuracionDispensacion(String productoid) {
		float tiempo = 0;

		////System.out.println("productid en retrieve duracion dispensacion " + productoid);
		try {

			//prepareConnection();

			Statement s = conn.createStatement();
			s
					.executeQuery("SELECT tiempo_dispensacion_lote FROM tipo_producto "
							+ "WHERE id = " + productoid);
			ResultSet rs = s.getResultSet();

			while (rs.next()) {

				tiempo = rs.getFloat("tiempo_dispensacion_lote");

			}
			rs.close();
			s.close();

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}

		return tiempo;
	}

	public float retrieveProductoDuracionProduccion(String productoid) {
		float tiempo = 0;

		try {

			//prepareConnection();

			Statement s = conn.createStatement();
			s.executeQuery("SELECT tiempo_produccion_lote FROM tipo_producto "
					+ "WHERE id = " + productoid);
			ResultSet rs = s.getResultSet();

			while (rs.next()) {

				tiempo = rs.getFloat("tiempo_produccion_lote");

			}
			rs.close();
			s.close();

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}

		return tiempo;
	}

	public float retrieveProductoDuracionLlenado(int unidades,
			String maquinaid, String productid) {
		float tiempo = 0;

		try {

			//prepareConnection();

			Statement s = conn.createStatement();
			String query = "SELECT tiempo_unidades FROM capacidad_de_producir WHERE "
					+ "tipo_producto_id = "
					+ productid
					+ " AND maquina_id = "
					+ maquinaid + " AND fase = " + "4";
			s.executeQuery(query);
			ResultSet rs = s.getResultSet();

			while (rs.next()) {

				tiempo = rs.getFloat("tiempo_unidades");

			}
			rs.close();
			s.close();

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}

		return unidades / tiempo;
	}
	
	public float retrieveProductoDuracionLoteado(int unidades,
			String maquinaid, String productid) {
		float tiempo = 0;

		try {

			//prepareConnection();

			Statement s = conn.createStatement();
			String query = "SELECT tiempo_unidades FROM capacidad_de_producir WHERE "
					+ "tipo_producto_id = "
					+ productid
					+ " AND maquina_id = "
					+ maquinaid + " AND fase = " + "5";
			s.executeQuery(query);
			ResultSet rs = s.getResultSet();

			while (rs.next()) {

				tiempo = rs.getFloat("tiempo_unidades");

			}
			rs.close();
			s.close();

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}

		return unidades / tiempo;
	}
	
	public float retrieveProductoDuracionEtiquetado(int unidades, String productid) {
		float unidadesporhora = 0;

		try {

			//prepareConnection();

			Statement s = conn.createStatement();
			String query = "SELECT unidades_etiquetadas_hora FROM tipo_producto WHERE "
					+ "id = "
					+ productid;
			s.executeQuery(query);
			ResultSet rs = s.getResultSet();

			while (rs.next()) {

				unidadesporhora = rs.getFloat("unidades_etiquetadas_hora");

			}
			rs.close();
			s.close();

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}

		return unidadesporhora;
	}

	public float retrieveProductoDuracionBandas(int unidades, String productid) {
		float unidadesporhora = 0;

		try {

			//prepareConnection();

			Statement s = conn.createStatement();
			String query = "SELECT unidades_bandas_hora FROM tipo_producto WHERE "
					+ "id = "
					+ productid;
			s.executeQuery(query);
			ResultSet rs = s.getResultSet();

			while (rs.next()) {

				unidadesporhora = rs.getFloat("unidades_bandas_hora");

			}
			rs.close();
			s.close();

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}

		return unidadesporhora;
	}
	
	public float retrieveProductoDuracionEmpaque(int unidades, String productid) {
		float unidadesporhora = 0;

		try {

			//prepareConnection();

			Statement s = conn.createStatement();
			String query = "SELECT unidades_empacadas_hora FROM tipo_producto WHERE "
					+ "id = "
					+ productid;
			s.executeQuery(query);
			ResultSet rs = s.getResultSet();

			while (rs.next()) {

				unidadesporhora = rs.getFloat("unidades_empacadas_hora");

			}
			rs.close();
			s.close();

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}

		return unidadesporhora;
	}

	@SuppressWarnings("unchecked")
	public String getMaquinariaValuesForProducto(int id, int index1, int index2) {

		listOfMaquinariaValues = new ArrayList<ArrayList<String>>();
		ArrayList<String> valueList = new ArrayList<String>();

		try {
			//prepareConnection();

			Statement s = conn.createStatement();
			s
					.executeQuery("SELECT DISTINCT modelo, tiempo_unidades, fase, maquina_id FROM capacidad_de_producir cp, "
							+ "maquina mq WHERE cp.maquina_id=mq.id AND (cp.fase = 3 OR cp.fase = 4 OR cp.fase = 5) "
							+ "AND cp.tipo_producto_id = "
							+ id
							+ " ORDER BY fase");

			ResultSet secondset = s.getResultSet();

			String past = "0", current = "0";
			while (secondset.next()) {
				past = current.toString();
				current = secondset.getString("fase");

				if (current.matches("3")) {
					valueList.add("3" + ":" + secondset.getString("maquina_id")
							+ ":" + secondset.getString("tiempo_unidades"));
					// //System.out.println("3valuelist=" + valueList);
				} else if (current.matches("4")) {
					if (past.matches("3")) {
						listOfMaquinariaValues
								.add((ArrayList<String>) valueList.clone());
						valueList.clear();
					}
					valueList.add("4" + ":" + secondset.getString("maquina_id")
							+ ":" + secondset.getString("tiempo_unidades"));
					// //System.out.println("4valuelist=" + valueList);
				} else if (current.matches("5")) {
					if (past.matches("4")) {
						listOfMaquinariaValues
								.add((ArrayList<String>) valueList.clone());
						valueList.clear();
					}
					valueList.add("5" + ":" + secondset.getString("maquina_id")
							+ ":" + secondset.getString("tiempo_unidades"));
					// //System.out.println("5valuelist=" + valueList);
				}
			}

			listOfMaquinariaValues.add((ArrayList<String>) valueList.clone());

			// //System.out.println("listofmaq"
			// + listOfMaquinariaValues.toArray().toString());
			// //System.out.println("index2=" + index2 + " index1= " + index1
			// + " selected"
			// + listOfMaquinariaValues.get(index1).toString());

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}

		// return listOfMaquinariaValues.get(index1).get(index2);
		return (listOfMaquinariaValues.get(index1)).get(index2);
	}

	@SuppressWarnings("unchecked")
	public String getMaquinariaValuesForFuncionario(int id, int index1,
			int index2) {

		listOfMaquinariaValues = new ArrayList<ArrayList<String>>();
		ArrayList<String> valueList = new ArrayList<String>();

		try {
			//prepareConnection();

			Statement s = conn.createStatement();
			s
					.executeQuery("SELECT DISTINCT modelo, maquina_id FROM capacidad_de_operar co, "
							+ "maquina mq WHERE co.maquina_id=mq.id AND co.funcionario_id = "
							+ id);

			ResultSet secondset = s.getResultSet();

			// String past = "0", current = "0";
			while (secondset.next()) {
				valueList.add(secondset.getString("maquina_id"));
			}

			listOfMaquinariaValues.add((ArrayList<String>) valueList.clone());

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}

		// return listOfMaquinariaValues.get(index1).get(index2);
		return (listOfMaquinariaValues.get(index1)).get(index2);
	}
	
	public ArrayList<String> getAllMaquinariaValuesForFuncionario(int id, int index1) {

		listOfMaquinariaValues = new ArrayList<ArrayList<String>>();
		ArrayList<String> valueList = new ArrayList<String>();

		try {
			//prepareConnection();

			Statement s = conn.createStatement();
			s
					.executeQuery("SELECT DISTINCT modelo, maquina_id FROM capacidad_de_operar co, "
							+ "maquina mq WHERE co.maquina_id=mq.id AND co.funcionario_id = "
							+ id);

			ResultSet secondset = s.getResultSet();

			// String past = "0", current = "0";
			while (secondset.next()) {
				valueList.add(secondset.getString("maquina_id"));
			}

			listOfMaquinariaValues.add((ArrayList<String>) valueList.clone());

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}

		// return listOfMaquinariaValues.get(index1).get(index2);
		return (listOfMaquinariaValues.get(index1));
	}

	public synchronized void insertMensuales(ArrayList<String> mensualData) {

		try {

			//prepareConnection();

			Statement s = conn.createStatement();

			/*
			 * //System.out.println(
			 * "INSERT INTO produccion_mensual (tipo_producto_id, prioridad" +
			 * ", unidades_mes) VALUES (" + mensualData.get(0) + "," +
			 * mensualData.get(1) + "," + mensualData.get(2) + ")");
			 */
			s
					.executeUpdate("INSERT INTO produccion_mensual (tipo_producto_id, prioridad"
							+ ", unidades_mes, dia, mes ,ano,unidades_por_producir) VALUES ("
							+ mensualData.get(0)
							+ ","
							+ mensualData.get(1)
							+ ","
							+ mensualData.get(2)
							+ ",'"
							+ mensualData.get(3)
							+ "','"
							+ mensualData.get(4)
							+ "','" 
							+ mensualData.get(5) 
							+ "'," 
							+ mensualData.get(6) 
							+")");

			s.close();

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}
	}

	public synchronized void deleteMensuales(String[] values) {

		try {

			//prepareConnection();

			// listOfFuncionarios = new ArrayList<ArrayList<String>>();

			Statement s = conn.createStatement();
			s.executeUpdate("DELETE FROM produccion_mensual WHERE id ="
					+ values[0]);
			s.close();

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}
	}
	
	

	public synchronized ArrayList<ArrayList<String>> retrieveMensuales(
			String dia, String mes, String ano) {
		try {

			//prepareConnection();

			listOfMensuales = new ArrayList<ArrayList<String>>();

			Statement s = conn.createStatement();
			s
					.executeQuery("SELECT id, tipo_producto_id, unidades_mes, prioridad FROM produccion_mensual "
							+ "WHERE dia = '"
							+ dia
							+ "' AND mes = '"
							+ mes
							+ "' AND ano = '" + ano + "'");
			ResultSet rs = s.getResultSet();

			while (rs.next()) {
				ArrayList<String> singleList = new ArrayList<String>();

				int tipo_producto_id = rs.getInt("tipo_producto_id");
				int id = rs.getInt("id");

				singleList.add(Integer.toString(id));

				Statement s2 = conn.createStatement();
				s2.executeQuery("SELECT nombre,unidad FROM tipo_producto "
						+ "WHERE id = " + tipo_producto_id);

				// //System.out.println("SELECT nombre,unidad FROM tipo_producto "
				// + "WHERE id = " + tipo_producto_id);
				ResultSet secondset = s2.getResultSet();

				secondset.next();
				singleList.add(secondset.getString("nombre"));
				singleList.add(Integer.toString(secondset.getInt("unidad"))
						+ " cc");
				singleList.add(Integer.toString(rs.getInt("unidades_mes")));
				switch (rs.getInt("prioridad")) {
				case 0:
					singleList.add("Alta");
					break;
				case 1:
					singleList.add("Media");
					break;
				case 2:
					singleList.add("Baja");
					break;
				}
				singleList.add(Integer.toString(rs.getInt("prioridad")));
				// singleList.add(rs.getString("mes"));
				// singleList.add(rs.getString("ano"));

				listOfMensuales.add(singleList);
				secondset.close();
			}
			rs.close();
			s.close();

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}

		return listOfMensuales;
	}

	public synchronized ArrayList<ArrayList<String>> retrieveListMensuales(
			String dia, String mes, String ano) {

		listOfMensuales = new ArrayList<ArrayList<String>>();

		try {

			//prepareConnection();

			Statement s = conn.createStatement();
			String statement = "SELECT id, tipo_producto_id, unidades_mes, prioridad, unidades_por_producir FROM produccion_mensual "
					+ "WHERE dia = '"
					+ dia
					+ "' AND mes = '"
					+ mes
					+ "' AND ano = '" + ano + "' ORDER BY prioridad ASC";
			s.executeQuery(statement);
			//System.out.println(statement);
			ResultSet rs = s.getResultSet();

			while (rs.next()) {
				ArrayList<String> singleList = new ArrayList<String>();

				int tipo_producto_id = rs.getInt("tipo_producto_id");
				int id = rs.getInt("id");

				singleList.add(Integer.toString(id));

				Statement s2 = conn.createStatement();
				s2.executeQuery("SELECT nombre,unidad FROM tipo_producto "
						+ "WHERE id = " + tipo_producto_id);

				// //System.out.println("SELECT nombre,unidad FROM tipo_producto "
				// + "WHERE id = " + tipo_producto_id);
				ResultSet secondset = s2.getResultSet();

				secondset.next();
				singleList.add(Integer.toString(tipo_producto_id));
				singleList.add(secondset.getString("nombre"));
				singleList.add(Integer.toString(secondset.getInt("unidad")));
				singleList.add(Integer.toString(rs.getInt("unidades_mes")));
				switch (rs.getInt("prioridad")) {
				case 0:
					singleList.add("Alta");
					break;
				case 1:
					singleList.add("Media");
					break;
				case 2:
					singleList.add("Baja");
					break;
				}
				// singleList.add(Integer.toString(rs.getInt("prioridad")));
				singleList.add(Integer.toString(rs.getInt("unidades_por_producir")));
				listOfMensuales.add(singleList);
				secondset.close();
			}
			rs.close();
			s.close();

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}

		return listOfMensuales;
	}
	
	public synchronized void modifyMensualesPorProducir(int value, String id, boolean add) {

		try {

			String addorsubtract = (add)?" - ":(" + ");
			Statement s = conn.createStatement();
			String query = "UPDATE produccion_mensual SET unidades_por_producir = unidades_por_producir " + addorsubtract + value + " WHERE id = " + id;
			s.executeUpdate(query);

			s.close();

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}
	}
	
	public synchronized void modifyLoteisDone(int id, int planday, int donedia, boolean done) {

		try {

			Statement s = conn.createStatement();
			String query = "UPDATE lote SET donedia = " + donedia + ", done = "  + done + " WHERE id = " + id + "  AND planday = " + planday;
			s.executeUpdate(query);

			s.close();

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}
	}

	public synchronized void modifyLoteDay(int id, int planday, int dia) {

		try {

			Statement s = conn.createStatement();
			String query = "UPDATE lote SET day = " + dia + " WHERE id = " + id + " AND planday = " + planday;
			s.executeUpdate(query);

			s.close();

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}
	}
	
	public synchronized void modifyLoteHour(int id, int planday, int hora) {

		try {

			Statement s = conn.createStatement();
			String query = "UPDATE lote SET hora = " + hora + " WHERE id = " + id + " AND planday = " + planday;
			s.executeUpdate(query);

			s.close();

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}
	}
	
	public synchronized Object retrieveLotes(int day, String month,
			String year,boolean status) {
		
		
		Planner plan = new Planner(day,month,year,"mostrar",0);

		try {

			//prepareConnection();

			Statement s = conn.createStatement();

			String query = "SELECT id,hora, produccion_mensual_id,day,planday,unidades,tipo_producto_id,tipo_producto_name,litros,donedia,done,startingfase FROM lote WHERE month = '"
					+ month
					+ "' AND year = '"
					+ year
					+ "' AND planday = "
					+ day
					+ " ORDER BY id ASC";
		
			s.executeQuery(query);

			ResultSet rs = s.getResultSet();
			while (rs.next()) {

				int id = rs.getInt("id");
				int hora = rs.getInt("hora");
				int planday = rs.getInt("planday");
				int startingfase = rs.getInt("startingfase");
				int produccionmensualid = rs.getInt("produccion_mensual_id");
				int unidades = rs.getInt("unidades");
				int daylote = rs.getInt("day");
				int tipoproductoid = rs.getInt("tipo_producto_id");
				int litros = rs.getInt("litros");
				String tipoproductoname = rs.getString("tipo_producto_name");
				int donedia = rs.getInt("donedia");
				boolean done = rs.getBoolean("done");

				if(daylote == 3 && planday == 3) {
					//system.out.println("watch here");
				}
				
				plan.addLote(id,hora,daylote, String
						.valueOf(produccionmensualid), litros, unidades, String
						.valueOf(tipoproductoid), tipoproductoname, done, donedia,startingfase);
				

				Statement s2 = conn.createStatement();
				
				String test = (status)?(" AND proyectado_o_real = " + status):("");
				
				String fases = "SELECT id, proyectado_o_real, hora_inicio, hora_fin, maquina_id, maquina_name, funcionario_id, funcionario_name, " +
				"planday,dia, mes, ano, fase_id, finalizada,unidades_anteriores,unidades_slot,unidades_pendientes FROM slot_produccion WHERE lote_id = "
				+ id + " AND planday = " + planday + test + " ORDER BY dia ASC, fase_id ASC, hora_inicio ASC, funcionario_name ASC"; 
				
				s2.executeQuery(fases);

				
				ResultSet secondset = s2.getResultSet();

				int lastfase = startingfase;
				int lastdia = daylote;
				int lasthorainicio = hora;
				
				ArrayList<String> funcionarioidlist = new ArrayList<String>();
				ArrayList<String> funcionarionamelist = new ArrayList<String>();
				
				int slotid = 0, horainicio = 0, horafin = 0, funcionarioid = 0, dia = 0, fase = 0, unidadesanteriores =0,unidadesslot =0 ,unidadespendientes = 0;
				String maquinaname = "", maquinaid = "", funcionarioname = "", mes = "" , ano = "";
				boolean proyectadooreal = false, finalizada = false;
				
				while (secondset.next()) {

					dia = secondset.getInt("dia");
					fase = secondset.getInt("fase_id");
					horainicio = secondset.getInt("hora_inicio");
					
					if(/*lastfase==fase && lastdia == dia*/lasthorainicio == horainicio && lastfase == fase && lastdia == dia) {
						
					}
					else {
						if(id == 4) {
							////system.out.println("watch here");
						}
						plan.lotes.get(id).addSlot(lastdia,ano,mes,lasthorainicio,horafin,
								maquinaid,maquinaname,
								funcionarioidlist,
								funcionarionamelist,
								lastfase,finalizada,
								unidadesanteriores,unidadesslot,unidadespendientes);
						
						funcionarioidlist = new ArrayList<String>();
						funcionarionamelist = new ArrayList<String>();
						
						
						
					}
					
					slotid = secondset.getInt("id");
					proyectadooreal = secondset.getBoolean("proyectado_o_real");
					finalizada = secondset.getBoolean("finalizada");
					horainicio = secondset.getInt("hora_inicio");
					horafin = secondset.getInt("hora_fin");
					maquinaid = String.valueOf(secondset.getInt("maquina_id"));
					maquinaname = secondset.getString("maquina_name");
					funcionarioid = secondset.getInt("funcionario_id");
					funcionarioname = secondset.getString("funcionario_name");
					mes = secondset.getString("mes");
					ano = secondset.getString("ano");
					unidadesanteriores = secondset.getInt("unidades_anteriores");
					unidadesslot = secondset.getInt("unidades_slot");
					unidadespendientes = secondset.getInt("unidades_pendientes");
					
					funcionarioidlist.add(String.valueOf(funcionarioid));
					funcionarionamelist.add(funcionarioname);
					
					
					lasthorainicio = horainicio;
					lastfase = fase;
					lastdia = dia;
					if(id == 4) {
						////System.out.println("watch here");
					}
					
				}
				
				plan.lotes.get(id).addSlot(dia,ano,mes,horainicio,horafin,
						maquinaid,maquinaname,
						funcionarioidlist,
						funcionarionamelist,
						fase,finalizada,
						unidadesanteriores,unidadesslot,unidadespendientes);

				secondset.close();

				s2.close();
				// s3.close();
				// s4.close();
			}
			rs.close();
			s.close();

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}

		return plan.lotes.clone();

	}

	
	synchronized int confirmLoteReal(int id) {
	
		try {

			//prepareConnection();

			Statement s = conn.createStatement();

			String query = "UPDATE lote SET status = 1 WHERE id = " + id;
			s.executeUpdate(query);


		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}

		return id;
	}
	
	public synchronized void changeStatusOfLote(int planday, int startday, int loteday, String month, String year, boolean status) {
		
		try {

			//prepareConnection();

			Statement s = conn.createStatement();

			String query = "UPDATE lote SET status = " + status +
					" WHERE day = " + loteday + " AND planday = " + planday + " AND month = '" + month + "' AND year  = '" + year + "'";
			s.executeUpdate(query);
			s.close();
		
			s = conn.createStatement();
			query = "SELECT id,produccion_mensual_id,unidades,day,startingfase FROM lote WHERE status = " + status + " AND "
			+ " day <= " + loteday + " AND planday = " + planday + " AND day >= " + startday + " AND month = '" + month + "' AND year  = '" + year + "'";
			
			s.executeQuery(query);

			ResultSet rs = s.getResultSet();
			int id = 0;
			int produccionid = 0;
			int unidades = 0;
			int deleteday = 0;
			int startingfase = 1;
			while (rs.next()) {
		
				id = rs.getInt("id");
				produccionid = rs.getInt("produccion_mensual_id");
				unidades = rs.getInt("unidades");
				deleteday = rs.getInt("day");
				startingfase = rs.getInt("startingfase");
				
				if(deleteday == loteday && startingfase == 1)
					modifyMensualesPorProducir(unidades,String.valueOf(produccionid),status);
		
				/*Statement s2 = conn.createStatement();
				
				query = "UPDATE slot_produccion SET proyectado_o_real = " + status +
				" WHERE dia = " + loteday + " AND mes = '" + month + "' AND ano  = '" + year + "' AND lote_id = " + id;*/
				
				/*s2.executeUpdate(query);
				s2.close();*/
			}
			
			Statement s2 = conn.createStatement();
			if(!status) {
				query = "UPDATE slot_produccion SET proyectado_o_real = " + status +
				" WHERE dia >= " + loteday + " AND mes = '" + month + "' AND ano  = '" + year + "' AND planday = " + planday;
			}
			else {
					query = "UPDATE slot_produccion SET proyectado_o_real = " + status +
					" WHERE dia <= " + loteday + " AND mes = '" + month + "' AND ano  = '" + year + "' AND planday = " + planday;
			}
			
			s2.executeUpdate(query);
			s2.close();
			
			s.close();
			
			
			


		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}
	}
	
	synchronized int insertLote(int hora, int day, int planday, String month, String year,
			String produccionid, String productid, String productname,
			int unidades, int litros,int status,boolean done, int donedia, int startingfase) {

		int id = 0;

		try {

			//prepareConnection();

			Statement s = conn.createStatement();

			String query = "INSERT INTO lote (hora,day,planday,month,year,produccion_mensual_id,tipo_producto_id,tipo_producto_name,unidades,litros,status,done,donedia,startingfase) "
					+ "VALUES ("
					+ hora
					+ ","
					+ day
					+ ","
					+ planday
					+ ",'"
					+ month
					+ "','"
					+ year
					+ "',"
					+ produccionid
					+ ","
					+ productid
					+ ",'"
					+ productname
					+ "'," 
					+ unidades 
					+ "," 
					+ litros 
					+ "," 
					+ status 
					+ "," 
					+ done 
					+ "," 
					+ donedia 
					+ "," 
					+ startingfase
					+ ")";
			////System.out.println("insert lote = " + query);
			s.executeUpdate(query);

			s
					.executeQuery("SELECT id AS LastID FROM lote WHERE id = @@Identity");

			ResultSet rs = s.getResultSet();
			while (rs.next()) {

				id = rs.getInt("LastID");

			}

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}

		return id;
	}
	
	synchronized int insertLote(int hora, int day, int planday, String month, String year,
			String produccionid, String productid, String productname,
			int unidades, int litros,int status,boolean done, int donedia, int startingfase, int loteid) {

		int id = 0;

		try {

			//prepareConnection();

			Statement s = conn.createStatement();

			String query = "INSERT INTO lote (id,hora,day,planday,month,year,produccion_mensual_id,tipo_producto_id,tipo_producto_name,unidades,litros,status,done,donedia,startingfase) "
					+ "VALUES ("
					+ loteid
					+ ","
					+ hora
					+ ","
					+ day
					+ ","
					+ planday
					+ ",'"
					+ month
					+ "','"
					+ year
					+ "',"
					+ produccionid
					+ ","
					+ productid
					+ ",'"
					+ productname
					+ "'," 
					+ unidades 
					+ "," 
					+ litros 
					+ "," 
					+ status 
					+ "," 
					+ done 
					+ "," 
					+ donedia 
					+ "," 
					+ startingfase
					+ ")";
			////System.out.println("insert lote = " + query);
			s.executeUpdate(query);

			s
					.executeQuery("SELECT id AS LastID FROM lote WHERE id = @@Identity");

			ResultSet rs = s.getResultSet();
			while (rs.next()) {

				id = rs.getInt("LastID");

			}

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}

		return id;
	}
	
	public synchronized int deleteAllLotesFromWeek(int day, String month, String year) {

		int id = 0;

		try {

			//prepareConnection();

			Statement s = conn.createStatement();

			String query = "DELETE FROM lote WHERE planday = " + day + " AND month = '" + month + "' AND year = '" + year + "' AND status = 0";
			s.executeUpdate(query);
			
			query = "ALTER TABLE lote AUTO_INCREMENT = 1";
			s.executeUpdate(query);
			
			query = "DELETE FROM slot_produccion WHERE planday = " + day + " AND mes = '" + month + "' AND ano = '" + year + "'" +
			" AND proyectado_o_real = 0";
			
			s.executeUpdate(query);
			
			


		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}

		return id;
	}
	
	

	synchronized void insertSlot(int starttime, int endtime, String maquinaid,
			String maquinaname, ArrayList<String> funcionarioid,
			ArrayList<String> funcionarioname, int faseid, boolean finalizada, int dia, int planday, String mes,
			String ano, int loteid, boolean proyectadooreal,
			int unidadesanteriores, int unidadesslot, int unidadespendientes) {

		String query = "", query2 = "";
		try {

			//prepareConnection();
			/*if(proyectadooreal == true) {
				Statement s = conn.createStatement();

				query = "DELETE FROM slot_produccion WHERE dia >= " + dia + " AND planday = " + planday + " AND mes = '" + mes + "' AND ano = '" + ano + "'" +
				" AND lote_id = " + loteid + " AND fase_id = " + faseid;
				
				s.executeUpdate(query);
			}*/
			

			for (int i = 0; i < funcionarioid.size(); i++) {
				Statement s = conn.createStatement();

				query2 = "INSERT INTO slot_produccion (hora_inicio, hora_fin, maquina_id,maquina_name, funcionario_id," +
						"funcionario_name, fase_id, dia,planday, mes, ano, proyectado_o_real,lote_id,finalizada,unidades_anteriores,unidades_slot,unidades_pendientes) "
						+ "VALUES ("
						+ starttime
						+ ","
						+ endtime
						+ ","
						+ maquinaid
						+ ",'"
						+ maquinaname
						+ "',"
						+ funcionarioid.get(i)
						+ ",'"
						+ funcionarioname.get(i)
						+ "',"
						+ faseid
						+ ","
						+ dia
						+ ","
						+ planday
						+ ",'"
						+ mes
						+ "','"
						+ ano
						+ "',"
						+ proyectadooreal
						+ "," 
						+ loteid 
						+ "," 
						+ finalizada 
						+ ","
						+ unidadesanteriores
						+ "," 
						+ unidadesslot 
						+ "," 
						+ unidadespendientes 
						+ ")";

				////System.out.println("insert slot query" + query2);
				s.executeUpdate(query2);
				s.close();
			}

		} catch (Exception e) {
			
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}
	}
	
	public int retrieveNextLoteID() {
		
		int nextloteid = 0;

		try {

			//prepareConnection();

			Statement s = conn.createStatement();
			String query = "SELECT id FROM lote ORDER BY id DESC";
			s.executeQuery(query);
			ResultSet rs = s.getResultSet();

			if(rs.next()) {

				nextloteid = rs.getInt("id");

			}
			
			rs.close();
			s.close();

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}

		return nextloteid;
	}
	
	public synchronized void insertDiaConfirmado(int dia, int planday, String mes, String ano) {

		try {

			//prepareConnection();

			Statement s = conn.createStatement();

			String query = "INSERT INTO diasconfirmados (planday,dia,mes,ano) "
					+ "VALUES ("
					+ planday
					+ ","
					+ dia
					+ ",'"
					+ mes
					+ "','"
					+ ano
					+ "')";
			////System.out.println("insert lote = " + query);
			s.executeUpdate(query);

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}

	}
	
	public synchronized void deleteDiaConfirmado(int dia, int planday, String mes, String ano) {

		try {

			//prepareConnection();

			Statement s = conn.createStatement();

			s.executeUpdate("DELETE FROM diasconfirmados WHERE dia = "
					+ dia + " AND mes = '" + mes + "' AND ano = '" + ano + "' AND planday = " + planday);

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}

	}
	
	public synchronized int retrieveLastDiaConfirmado(int planday, String mes, String ano) {

		int ultimodia = 0;

		try {

			//prepareConnection();

			Statement s = conn.createStatement();
			String query = "SELECT dia FROM diasconfirmados WHERE mes = '" + mes + "' AND ano = '" + ano + 
			"' AND planday = " + planday + " ORDER BY dia DESC";
			s.executeQuery(query);
			
			ResultSet rs = s.getResultSet();

			if(rs.next()) {

				ultimodia = rs.getInt("dia");

			}
			
			rs.close();
			s.close();

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}

		return ultimodia;
	}
	
	public synchronized int retrieveIfSpecificDiaConfirmado(int dia, int planday, String mes, String ano) {

		int ultimodia = 0;

		try {

			//prepareConnection();

			Statement s = conn.createStatement();
			String query = "SELECT dia FROM diasconfirmados WHERE mes = '" + mes + "' AND ano = '" + ano + 
			"' AND dia = " + dia + " AND planday = " + planday;
			s.executeQuery(query);
			ResultSet rs = s.getResultSet();

			if(rs.next()) {

				ultimodia = rs.getInt("dia");

			}
			
			rs.close();
			s.close();

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}

		return ultimodia;
	}
	
	public synchronized int countWeekConfirmados(int dia, String mes, String ano) {

		int countdias = 0;

		try {

		
			//prepareConnection();

			Statement s = conn.createStatement();
			String query = "SELECT COUNT(*) AS dia  FROM diasconfirmados WHERE mes = '" + mes + "' AND ano = '" + ano + 
			"' AND planday = " + dia + " ORDER BY dia DESC";
			s.executeQuery(query);
			ResultSet rs = s.getResultSet();

			if(rs.next()) {

				countdias = rs.getInt("dia");

			}
			
			rs.close();
			s.close();

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}

		////System.out.println(" dias semana anterior " +  countdias);
		return countdias;
	}
	
	
/*	public synchronized int actionToPerform(int primerDiaSemana, int ultimoDiaSemana, String month, String year, String action) {

		int performDia = 0;

		try {

			//prepareConnection();

			Statement s = conn.createStatement();
			String query = "SELECT action_to_perform,perform_day FROM actions WHERE perform_month = '" + month + "' AND perform_year = '" + year + 
			"' AND perform_day >= " + primerDiaSemana + " AND perform_day <= " + ultimoDiaSemana + " AND action_to_perform = '" + action + "' " +
			" AND performed = 0 ORDER BY perform_day DESC";
			s.executeQuery(query);
			ResultSet rs = s.getResultSet();

			if(rs.next()) {

				performDia = rs.getInt("perform_day");

			}
			
			
			rs.close();
			s.close();

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}

		return performDia;
	}
	
	public synchronized void insertActionToPerform(int dia, String mes, String ano, String action) {

		try {

			//prepareConnection();

			Statement s = conn.createStatement();

			String query = "INSERT INTO actions (perform_day,perform_month,perform_year,action_to_perform) "
					+ "VALUES ("
					+ dia
					+ ",'"
					+ mes
					+ "','"
					+ ano
					+ "','"
					+ action
					+ "')";
			////System.out.println("insert lote = " + query);
			s.executeUpdate(query);

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}

	}
	
	public synchronized void performAction(int dia, String mes, String ano, String action) {

		try {

			//prepareConnection();

			Statement s = conn.createStatement();

			String query = "UPDATE actions SET performed = " + true +
			" WHERE perform_day = " + dia + " AND perform_month = '" + mes + "' AND perform_year  = '" + ano + "'" +
			" AND action_to_perform = '" + action + "'";
			
			////System.out.println("insert lote = " + query);
			s.executeUpdate(query);

		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			//closeConnection();
		}

	}*/
}
